package com.base.day9;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import com.base.day8.JDBCComon;
/**
 * 对产品表，进行增删改查
 * @author jiyu
 *
 */
public class ProductService {
	JDBCComon jdbc=new JDBCComon();
	Connection conn=null;
	/**
	 * 增加
	 * @param product
	 * @return
	 */
	public boolean add(Product product){
		boolean flag=false;
		try {
			conn=jdbc.getConnection();
			String sql="insert into product (name,category,price) values (?,?,?)";
			PreparedStatement st=conn.prepareStatement(sql);
			st.setString(1, product.getName());
			st.setString(2, product.getCategory());
			st.setBigDecimal(3, product.getPrice());
			if(st.execute()){
				flag=true;
			}
			st.close();
			conn.close();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	};
	
	/**
	 * 删除
	 * @param id
	 * @return
	 */
	public boolean remove(int id){
		boolean flag=false;
		try {
			conn=jdbc.getConnection();
			String sql="delete from product where id=?";
			PreparedStatement st=conn.prepareStatement(sql);
			st.setInt(1, id);
			if(st.execute()){
				flag=true;
			}
			st.close();
			conn.close();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}
	
	/**
	 * 修改
	 * @param product
	 * @return
	 */
	public boolean update(Product product){
		boolean flag=false;
		try {
			conn=jdbc.getConnection();
			String sql="update product set name=?,category=?,price=? where id=?";
			PreparedStatement st=conn.prepareStatement(sql);
			st.setString(1, product.getName());
			st.setString(2, product.getCategory());
			st.setBigDecimal(3, product.getPrice());
			st.setInt(4, product.getId());
			if(st.execute()){
				flag=true;
			}
			st.close();
			conn.close();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}
	
	/**
	 * 根据名字查找
	 * @param 产品名字
	 * @return
	 */
	public Product findByName(String productName){
		Product product=null;
		try {
			conn=jdbc.getConnection();
			String sql="select * from product where name=?";
			PreparedStatement st=conn.prepareStatement(sql);
			st.setString(1, productName);
			ResultSet rs=st.executeQuery();
			while(rs.next()){
				product=new Product();
				product.setCategory(rs.getString("category"));
				product.setId(rs.getInt("id"));
				product.setName(rs.getString("name"));
				product.setPrice(rs.getBigDecimal("price"));
			}
			rs.close();
			st.close();
			conn.close();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return product;
	}
	
	/**
	 * 查询所有产品
	 * @param 产品名字
	 * @return
	 */
	public ArrayList selectAll(){
		ArrayList productlist=new ArrayList();
		try {
			conn=jdbc.getConnection();
			String sql="select * from product";
			PreparedStatement st=conn.prepareStatement(sql);
			ResultSet rs=st.executeQuery();
			while(rs.next()){
				Product product=new Product();
				product.setCategory(rs.getString("category"));
				product.setId(rs.getInt("id"));
				product.setName(rs.getString("name"));
				product.setPrice(rs.getBigDecimal("price"));
				productlist.add(product);
			}
			rs.close();
			st.close();
			conn.close();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return productlist;
	}
	
	
	/**
	 * 增加
	 * @param product
	 * @return
	 */
	public boolean add2(Product product){
		boolean flag=false;
		Product p1=findByName(product.getName());
		if(p1==null){
			add(product);
			flag=true;
		}else{
			System.out.println("产品名字已经存在");
		}
		return flag;
	};
	

}
